#_*_ coding:UTF-8 _*_
import MySQLdb
import time
import random
import  copy
from DataCleaning import DataCleaning
class ToMysql:
    '''
    该类主要是向数据库中写入数据
    '''
    def __init__(self,host,port,user,passwd,db,shopId):
        '''
        初始化链接数据库所需要的参数
        :param host:（string）主机信息
        :param port:（int）端口
        :param user:（string）用户名
        :param passwd:（string）密码
        :param db:（string）数据库
        :param shopId:(string)店铺Id
        '''
        self.host=host
        self.port=port
        self.user=user
        self.passwd=passwd
        self.db=db
        self.shopId=shopId

    def getConn(self):
        '''
        获得链接数据库的句柄
        :return:链接数据库的句柄
        '''
        conn=MySQLdb.connect(host=self.host,port=self.port,user=self.user,passwd=self.passwd,db=self.db)
        # cur=conn.cursor()
        return  conn

    def writeSaleFellow(self,conn,allFollows,tablesName):
        '''
        向销售跟随关系表tt_salefollow_rltionsp中写入数据
        :param conn:数据库句柄
        :param allFollows:（dict）销售跟随映射
        :param tablesName:(string)
        :return:直接写入数据库
        '''
        cur = conn.cursor()
        sqli="insert into "+tablesName+ " (passerby_id,sale_id,shop_id,updatetime) values (%s,%s,%s,%s)"

        for consumerkey in allFollows:#策略1下得到的销售跟随
            salekey=int(allFollows[consumerkey].split(",")[0])
            cur.execute(sqli,(consumerkey,salekey,self.shopId,time.time()))
        # for camerakey in allFollows:#其他策略下得到的销售跟随
        #     for consumerkey in allFollows[camerakey]:
        #         salekey=int(allFollows[camerakey][consumerkey].split(",")[0])
        #         cur.execute(sqli,(consumerkey,salekey,self.shopId,time.time()))

        cur.close()
        conn.commit()
        conn.close()

    # def writePasserbyAction(self,conn,allRealID,cars,lookcar,board):#没有时间戳数据
    def writePasserbyAction(self, conn, allRealID, cars, lookcar, board,startInTime,startInShop,idMap,tableName):#有时间戳数据
        '''
        向行人车前行为表tt_passerby_action中写入数据
        :param conn:数据库句柄
        :param allRealID:（list）进店顾客真实id列表
        :param cars:(list)店中的车辆ID列表
        :param lookcar:（dict)看车顾客映射字典
        :param board:（dict）顾客上车关系映射
        :param startInTime:（dict）{临时真实ID：进店时的时间戳}
        :param startInShop:（dict）{临时真实ID：进店时的帧数}
        :param idMap:(dict)门口摄像头下临时ID和真实id的映射
        :param tableName:(string)
        :return:写入数据库
        '''
        cur = conn.cursor()
        dc=DataCleaning(conn)
        sqli="insert into "+tableName +" (passerby_id,car_id,is_carstay,stay_starttime,\
                                              stay_endtime,is_incar,updatetime)values (%s,%s,%s,%s,%s,%s,%s)"#插入数据语句

        for consumer in allRealID:#行人ID

            for car in cars:#车辆ID

                carID=car
                if lookcar.has_key(carID):
                    if lookcar[carID].has_key(consumer):
                        isCarStay="1"#是否车前停留

                        # 有时间戳数据
                        startFrame=lookcar[carID][consumer][0].split(",")[0]#开始停留的帧数
                        endFrame=lookcar[carID][consumer][-1].split(",")[0]#结束停留的帧数
                        for key in idMap:#找到与真实Id相对应的临时ID
                            if idMap[key] == consumer:
                                tmp = key
                                break
                            else:
                                tmp = " "
                        if startInShop.has_key(tmp) and startInTime.has_key(tmp):
                            startTime=dc.getTime(startFrame,startInTime[tmp],startInShop[tmp])#开始停留时间
                            endTime=dc.getTime(endFrame,startInTime[tmp],startInShop[tmp])#结束停留时间
                        else:
                            startTime="0"
                            endTime="0"

                        #没有时间戳数据
                        # startTime=lookcar[carID][consumer][0].split(",")[1]#开始停留的帧数
                        # endTime = lookcar[carID][consumer][-1].split(",")[1]  # 结束停留的帧数

                    else:
                        isCarStay="0"
                        startTime="0"
                        endTime="0"
                else:
                    isCarStay = "0"
                    startTime = "0"
                    endTime = "0"

                if board.has_key(carID):
                    if consumer in board[carID]:
                        isInCar="1"#车前停留是否进车
                    else:
                        isInCar = "0"
                else:
                    isInCar = "0"

                cur.execute(sqli,(consumer,car,isCarStay,startTime,endTime,isInCar,time.time()))

        cur.close()
        conn.commit()
        conn.close()

    def writePasserbyAction1(self, conn, allRealID, cars, lookcar,allIdMap,tableName,passerbyInfoTableName,unixTimestamp):#有时间戳数据
        '''
        向行人车前行为表tt_passerby_action中写入数据，只写入顾客看车的行为（看车），为了统计看车顾客的年龄段和性别
        :param conn:数据库句柄
        :param allRealID:（list）进店顾客真实id列表
        :param cars:(list)店中的车辆ID列表
        :param lookcar:（dict)看车顾客映射字典
        :param allIdMap:(dict)各个摄像头下临时ID和真实id的映射
        :param tableName:(string)
        :param passerbyInfoTableName:(string)存放行人性别年龄数据的表名
        :param unixTimestamp:当天视频的unix时间戳
        :return:写入数据库
        '''
        cur = conn.cursor()
        sqli="insert into "+tableName +" (passerby_id,car_id,is_carstay,stay_starttime,\
                    stay_endtime,is_incar,updatetime)values (%s,%s,%s,%s,%s,%s,%s)"#插入数据语句

        for consumer in allRealID:#行人ID
            #从行人信息表中读取passerby_id
            curTmp = conn.cursor()
            sql = "select passerby_info_id from "+passerbyInfoTableName+" where  real_id=\"" + consumer + "\""
            curTmp.execute(sql)
            result = str(curTmp.fetchall())  # 所属人群ID
            passerby_info= result.replace("(", "").replace("(", "").replace("L", "").replace(",", "").replace(")", ""). \
                replace(",", "").replace(")", "")
            curTmp.close()
            if passerby_info=="0":
                continue

            for car in cars:#车辆ID
                carID=car
                isCarStay = "0"  # 是否车前停留
                for camerakey in lookcar:
                    tmp=lookcar[camerakey]
                    if not tmp.has_key(carID):
                        continue
                    lookcarConsumer=tmp[carID].keys()
                    idMap=allIdMap[camerakey]
                    for con in lookcarConsumer:
                        if idMap[con]==consumer:
                            isCarStay = "1"

                startTime = unixTimestamp#开看车时间
                endTime = unixTimestamp#结束看车时间

                isInCar = "0"#是否上车

                cur.execute(sqli,(passerby_info,car,isCarStay,startTime,endTime,isInCar,time.time()))

        cur.close()
        conn.commit()
        conn.close()

    # def writeCrowdInfo(self,conn,family,potentialCon,startInShop):#没有时间戳数据
    def writeCrowdInfo(self, conn, family, potentialCon, startInTime,idMap,tableName):#有时间戳数据
        '''
        向人群信息表tm_crowd_info中写入数据（真实ID）
        :param conn:链接数据库的句柄
        :param family:（dict）人群数据：master：slaves
        :param potentialCon:（dict）潜在客户与意向度映射
        :param startInShop:（dict）顾客进店帧数映射
        :param startInTime:（dict）{临时真实ID：进店时的时间戳}
        :param idMap:(dict)门口摄像头下临时ID和真实id的映射
        :param tableName:(string)
        :return:写入数据库
        '''
        cur = conn.cursor()
        sqli = "insert into "+tableName+" (main_custr_id,man_num,shop_id,buy_car_intention,come_num,in_storetime,updatetime) VALUES (%s,%s,%s,%s,%s,%s,%s)"  # 插入数据语句


        for master in family:#主要购车者

            manNum=str(len(family[master]))#人数
            mumberID=",".join(family[master])#人群成员
            shopID=self.shopId#所属店铺ID
            if potentialCon.has_key(master):
                buyCarIntention=(potentialCon[master])#购车意向度
            else:
                buyCarIntention=str(random.random()/10.0)
            comeNum=1#来店次数
            for key in idMap:#找到与真实Id相对应的临时ID
                if idMap[key] == master:
                    tmp = key
                    break
                else:
                    tmp = " "
            if startInTime.has_key(tmp):#没有时间戳数据
                inStoreTime=startInTime[tmp]#进店时间
            # if startInTime.has_key(master):  # 有时间戳数据
            #     inStoreTime = startInTime[master]  # 进店时间
            else:
                inStoreTime="0"
            updateTime=time.time()#更新时间

            cur.execute(sqli,(master,manNum,shopID,buyCarIntention,comeNum,inStoreTime,updateTime))

        cur.close()
        conn.commit()
        conn.close()

    def writeCrowdInfo1(self, conn, family, potentialCon, startInTime,idMap,tableName):#有时间戳数据
        '''
        向人群信息表tm_crowd_info中写入数据(临时ID)
        :param conn:链接数据库的句柄
        :param family:（dict）人群数据：master：slaves
        :param potentialCon:（dict）潜在客户与意向度映射
        :param startInShop:（dict）顾客进店帧数映射
        :param startInTime:（dict）{临时真实ID：进店时的时间戳}
        :param idMap:(dict)门口摄像头下临时ID和真实id的映射
        :param tableName:(string)
        :return:写入数据库
        '''
        cur = conn.cursor()
        sqli = "insert into "+tableName+" (main_custr_id,man_num,shop_id,buy_car_intention,come_num,in_storetime,updatetime) VALUES (%s,%s,%s,%s,%s,%s,%s)"  # 插入数据语句


        for master in family:#主要购车者

            manNum=str(len(family[master]))#人数
            mumberID=",".join(family[master])#人群成员
            shopID=self.shopId#所属店铺ID
            if potentialCon.has_key(master):
                buyCarIntention=(potentialCon[master])#购车意向度
            else:
                buyCarIntention=str(random.random()/10.0)
            comeNum=1#来店次数
            # for key in idMap:#找到与真实Id相对应的临时ID
            #     if idMap[key] == master:
            #         tmp = key
            #         break
            #     else:
            #         tmp = " "
            # if startInTime.has_key(tmp):#没有时间戳数据
            #     inStoreTime=startInTime[tmp]#进店时间
            # # if startInTime.has_key(master):  # 有时间戳数据
            # #     inStoreTime = startInTime[master]  # 进店时间
            # else:
            #     inStoreTime="0"
            inStoreTime = "0"
            updateTime=time.time()#更新时间

            cur.execute(sqli,(master,manNum,shopID,buyCarIntention,comeNum,inStoreTime,updateTime))

        cur.close()
        conn.commit()
        conn.close()

    def writePasserbyInfo(self,conn,allRealId,startTime,potentialCon,crowdInfo,allFamily,slaveMaster,lookcar,board,idMap,allFollows,tableName):
        '''
        向行人信息表tm_passerby_info中写入数据
        :param conn:链接数据库的句柄
        :param allRealId:（list）全部顾客的真实ID列表
        :param startTime:(dict)顾客进店时的时间戳映射（临时ID）
        :param potentialCon:(dict)顾客与购车意向度之间的映射
        :param crowdInfo:（string）存储集体顾客信息的表
        :param allFamily:(dict)master:salves
        :param slaveMaster:(dict) slave:master
        :param idMap:(dict)门口摄像头下临时ID和真实id的映射
        :param allFollows:（dict）销售跟随映射
        :param tableName:(string)
        :return:将数据写入数据库
        '''
        cur = conn.cursor()
        # sqli = "insert into tm_passerby_info(passerby_info_id,customer_id,shop_id,\
        #         in_storetime,is_main_custr,buy_car_intention,intention_car_type,crowd_id) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)"  # 插入数据语句

        #更新数据
        sqli="update "+tableName+ " set real_id=%s, in_storetime=%s,is_main_custr=%s,buy_car_intention=%s,intention_car_type=%s,crowd_id=%s,\
                                            is_follow=%s,total_staytime=%s,is_ptetil_customer=%s,updatetime=%s   WHERE customer_id=%s "
        # sqli ="insert into tm_passerby_info(customer_id,real_id,in_storetime,is_main_custr,buy_car_intention,intention_car_type,crowd_id,updatetime,\
        #                                    is_follow,total_staytime,is_ptetil_customer )  VALUES  (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        for consumer in allRealId:#对于每个顾客
            curTmp=conn.cursor()
            # passerbyInfoID=int(consumer)#行人ID（店铺/时间戳+顺序/次数）标识（主键）
            shopId=self.shopId#所属4S店ID
            for key in idMap:#找到真实IDconsumer对应的临时ID
                if idMap[key]==consumer:
                    tmp=key
                    break
                else:
                    tmp=" "
            if startTime.has_key(tmp):
                inStoreTime=int(startTime[tmp])#进店时间
            else:
                inStoreTime=0

            if not allFamily.has_key(consumer):
                isMainCustr=0#是否为主购车者
                if slaveMaster.has_key(consumer):
                    master=slaveMaster[consumer]
                    sql = "select crowd_info_id from %s where shop_id=%s and master_id=%s" % (crowdInfo, shopId, master)
                    curTmp.execute(sql)
                    result =str(curTmp.fetchall())#所属人群ID
                    crowdID=result.replace("(","").replace("(","").replace("L","").replace(",","").replace(")","").\
                        replace(",","").replace(")","")
                    # print str(crowdID)+"   1"
                    # print consumer
                    curTmp.close()
                else:
                    crowdID=-1#不在人群中
            else:
                isMainCustr=1
                sql="select crowd_info_id from %s where shop_id=%s and master_id=%s"%(crowdInfo,shopId,consumer)
                curTmp.execute(sql)
                result =str(curTmp.fetchall())
                crowdID = result.replace("(", "").replace("(", "").replace("L", "").replace(",", "").replace(")" ,"")\
                    .replace(",", "").replace(")", "")
                # print str(crowdID)+"   2"
                # print consumer
                curTmp.close()
            if potentialCon.has_key(consumer):
                buyCarIntention=float(potentialCon[consumer])#购车意向度
            else:
                buyCarIntention=0.1

            if buyCarIntention>=0.4:
                isPtetilCustomer=1#是否是潜在客户
            else:
                isPtetilCustomer=0
            intentionCar=[]#意向车型
            #顾客看的车或者是上的车就是该客户的意向车型
            totalStayTime=0.0
            for carkey in lookcar:
                if lookcar[carkey].has_key(consumer) and carkey not in intentionCar:
                    intentionCar.append(carkey)#CAR1=>1,对车辆的id做一个转换，后期不需要这样的转换
                    totalStayTime+=len(lookcar[carkey][consumer])

            totalStayTime=totalStayTime/4.0

            for carkey in board:
                if consumer in board[carkey] and carkey not in intentionCar:
                    intentionCar.append(carkey.replace)

            intentionCarType=",".join(intentionCar)#意向车型

            if allFollows.has_key(consumer):
                isFollow=1#是否被销售跟随
            else:
                isFollow=0

            #更新数据库
            cur.execute(sqli,(consumer,str(inStoreTime),str(isMainCustr),str(buyCarIntention),\
                            str(intentionCarType),str(crowdID),isFollow,totalStayTime,isPtetilCustomer,time.time(),consumer))

        cur.close()
        conn.commit()
        conn.close()

    def writeSalesInfo(self,conn,saleID,tableName):
        '''
        向销售信息表tm_sales_info中写入数据
        :param conn:（string）链接数据库的句柄
        :param saleID:（list）销售ID
        :param tableName:(string)
        :return:写入数据库
        '''
        cur = conn.cursor()
        sqli = "insert into "+tableName+" (sales_info_id,shop_id,updatetime) VALUES (%s,%s,%s)"  # 插入数据语句

        for id in saleID:#销售ID
            cur.execute(sqli,(id,self.shopId,time.time()))

        cur.close()
        conn.commit()
        conn.close()

    def writePassengerFlow(self,conn,allRealID,videoTime,slaveMaster,tableName):
        '''
        向tt_passenger_flow表中写入数据（一家人是真实id）
        :param conn:链接数据库的句柄
        :param allRealID:（list）true_id列表
        :param videoTime:（string）视频的日期
        :param slaveMaster:（dict）{slave：master}
        :param tableName:(string)
        :return:向表中写入数据
        '''
        realID=copy.deepcopy(allRealID)
        for slave in slaveMaster:
            if slave in realID:
                index=realID.index(slave)
                realID[index]=slave

        num=len(set(realID))#客流量
        updateTime=time.time()#更新时间

        cur = conn.cursor()
        sqli = "insert into "+tableName+" (sts_time,num,shop_id,updatetime) VALUES (%s,%s,%s,%s)"  # 插入数据语句
        cur.execute(sqli, (videoTime, num,self.shopId, updateTime))

        cur.close()
        conn.commit()
        conn.close()

    def writePassengerFlow1(self,conn,consumerNum,videoTime,familly,fellows,tableName):
        '''
        向tt_passenger_flow表中写入数据（人群信息是临时Id，增加了销售跟随率的字段）
        :param conn:链接数据库的句柄
        :param allRealID:（list）true_id列表
        :param videoTime:（string）视频的日期
        :param familly:（list）[[一家人]]
        :param fellows:(dict)顾客ID：跟随的销售ID
        :param tableName:(string)
        :return:向表中写入数据
        '''

        num=consumerNum
        # for together in familly:
        #     l=len(together)
        #     num-=(l-1)#一群人只算一个人
        updateTime=time.time()#更新时间
        saleNum=len(fellows.keys())
        if  num==0:
            fellowRate=0
        else:
            fellowRate=float(saleNum/float(num))
        cur = conn.cursor()
        sqli = "insert into "+tableName+" (sts_time,num,shop_id,updatatime,follow_rate) VALUES (%s,%s,%s,%s,%s)"  # 插入数据语句
        cur.execute(sqli, (str(videoTime), num,self.shopId, updateTime,fellowRate))

        cur.close()
        conn.commit()
        conn.close()

    def writePassengerFlow2(self, conn, consumerNum, videoTime, familly, followRatio,tableName,batchNum):
        '''
        向tt_passenger_flow表中写入数据（人群信息是临时Id，增加了销售跟随率的字段，用临时id计算销售的跟随率）
        :param conn:链接数据库的句柄
        :param allRealID:（list）true_id列表
        :param videoTime:（string）视频的日期
        :param familly:（list）[[一家人]]
        :param followsRatio:(float)销售的跟随率
        :param tableName:(string)
        :param batchNum:(int)进店批次
        :return:向表中写入数据
        '''

        num = str(consumerNum)
        # for together in familly:
        #     l=len(together)
        #     num-=(l-1)#一群人只算一个人
        updateTime = time.time()  # 更新时间
        # saleNum = len(fellows.keys())
        # if num == 0:
        #     fellowRate = 0
        # else:
        #     fellowRate = float(saleNum / float(num))
        followRate = followRatio  # 顾客跟随率
        cur = conn.cursor()
        sqli = "insert into "+tableName+" (sts_time,num,shop_id,updatatime,follow_rate,batch_num) VALUES (%s,%s,%s,%s,%s,%s)"  # 插入数据语句
        cur.execute(sqli, (str(videoTime), num, self.shopId, updateTime, followRate,batchNum))

        cur.close()
        conn.commit()
        conn.close()

    def getConNum(self,conn,videoTime,tableName):
        '''
        获取人流数量
        :param conn:链接数据库的句柄
        :param videoTime:视频时间
        :param tableName:要查询的表名
        :return:
        '''
        cur = conn.cursor()
        sqli = "select count(*) from "+tableName+"  WHERE  vdotime = " +str(videoTime) # 插入数据语句
        cur.execute(sqli)
        result = str(cur.fetchall())
        consumerNum = result.replace("(", "").replace("(", "").replace("L", "").replace(",", "").replace(")", "") \
            .replace(",", "").replace(")", "")
        return consumerNum

    def writePassengerActionNum(self,conn,lookcarSum,boardSum,lookcarTime,boradTime,carsID,sysTime,tableName):
        '''
        向统计看车、上车数量时间的表中写入数据
        :param conn:链接数据库的句柄
        :param lookcarSum:（dict）看车人数
        :param boardSum:（dict）上车人数
        :param lookcarTime:（dict）看车时长
        :param boradTime:(dict）上车时长
        :param carsID:（list）车辆ID
        :param sysTime:时间戳（哪天的视频）
        :param tableName:(string)
        :return:写入数据库
        '''
        #线上代码的主键是我自己加的
        cur = conn.cursor()
        sqli = "insert into "+tableName+" (car_id,shop_id,car_batch_num,in_car_num,sts_time,car_duration_num,updatetime) VALUES (%s,%s,%s,%s,%s,%s,%s)"  # 插入数据语句

        for car in carsID:
            if lookcarSum.has_key(car):
               carBatchNum=lookcarSum[car]#看车人数
            else:
                carBatchNum =0
            if boardSum.has_key(car):
                inCarNum=boardSum[car]#上车人数
            else:
                inCarNum=0
            if lookcarTime.has_key(car):
                # 看车时间
                durationTime=lookcarTime[car]
            else:
                durationTime=0
            if boradTime.has_key(car):
                durationTime=durationTime+boradTime[car]

            durationTime=durationTime/4#4帧等于1秒
            cur.execute(sqli, (str(car), str(self.shopId), str(carBatchNum),str(inCarNum),str(sysTime),str(durationTime),str(time.time())))

        cur.close()
        conn.commit()
        conn.close()

    def getSaleNum(self,conn,saleList,tableName,videoTime):
        '''
        统计销售的数量
        :param conn: 链接数据库的句柄
        :param saleList: 销售真实ID列表
        :param tableName: （str）要查询的表名
        :return: 销售的数量
        '''
        cur = conn.cursor()
        # sqli = "select count(feature_id) from " + tableName + "  WHERE   feature_id=(%s) and vdotime = " +str(videoTime)  # 插入数据语句
        sum=0
        for sale in saleList:
            ss = str(sale)
            sqli = "select count(*) from "+tableName+" WHERE   feature_id= \""+ss+"\""
            cur.execute(sqli)
            result = str(cur.fetchall())
            saleTmp= result.replace("(", "").replace("(", "").replace("L", "").replace(",", "").replace(")", "") \
                .replace(",", "").replace(")", "")
            sum+=float(saleTmp)
        cur.close()
        conn.commit()
        conn.close()
        return sum



